package org.kl.bf.repository.basic;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.apache.commons.lang3.StringUtils;
import org.joda.time.DateTime;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

/**
 * @Author hqm
 * @CreateTime 2014年4月24日 下午9:01:48
 */
@Component
public class DBProcessExecuteDao {
	private JdbcTemplate jdbcTemp;

	@Resource
	public void setDataSource(DataSource dataSource) {
		jdbcTemp = new JdbcTemplate(dataSource);
	}

	/*
	 * @SuppressWarnings({ "unchecked", "rawtypes" }) public String
	 * getDocNo(final String djdm, final String dwdm) { final String
	 * callFunctionSql = "{call PUB.GetDJH(?,?)}"; List<SqlParameter> params =
	 * new ArrayList<SqlParameter>(); params.add(new
	 * SqlParameter(Types.VARCHAR)); params.add(new SqlReturnResultSet("result",
	 * new ResultSetExtractor<Integer>() {
	 * 
	 * @Override public Integer extractData(ResultSet rs) throws SQLException,
	 * DataAccessException { while(rs.next()) { return rs.getInt(1); } return 0;
	 * })); Map<String, Object> outValues = jdbcTemplate.call( new
	 * CallableStatementCreator() {
	 * 
	 * @Override public CallableStatement createCallableStatement(Connection
	 * conn) throws SQLException { CallableStatement cstmt =
	 * conn.prepareCall(callFunctionSql); cstmt.setString(1, "test"); return
	 * cstmt; }}, params); Assert.assertEquals(4, outValues.get("result")); }
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public String getDocNo(final String djdm, final String dwdm) {
		return (String) jdbcTemp.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				String storedProc = "{? = call PUB.GetDJH(?,?)}";// 调用的sql
				CallableStatement cs = con.prepareCall(storedProc);
				cs.setString(2, djdm);// 设置输入参数的值
				cs.setString(3, dwdm);// 设置输入参数的值
				cs.registerOutParameter(1, Types.VARCHAR);// 注册输出参数的类型
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				cs.execute();
				return cs.getString(1);// 获取输出参数的值
			}
		});
	}

	/**
	 * 单据号前缀+拼接符+年月日（yyyyMMdd）+4位序列号 获取例子：DD-201502270001
	 * 
	 * @param djdm
	 *            单据号前缀
	 * @param dwdm
	 *            拼接符
	 * @param xlws
	 *            序列保留位数
	 * @return
	 */
	public String getDocNoForMySQL(String djdm, String dwdm, int xlws) {
		Integer seq = jdbcTemp.queryForObject("select nextval('" + djdm + "')", Integer.class);
		if (seq > 9999) {
			seq = jdbcTemp.queryForObject("select setval ('" + djdm + "', 1)", Integer.class);
		}
		String docNo = djdm + dwdm + new DateTime().toString("yyMMdd") + StringUtils.leftPad(seq + "", xlws, "0");
		return docNo;
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public String CheckAttendanceDate(final String startDate, final String endDate,final String empId) {
		return (String) jdbcTemp.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				String storedProc = "{? = call ATTENDANCE.CheckAttendanceDate(?,?,?)}";// 调用的sql
				CallableStatement cs = con.prepareCall(storedProc);
				cs.setString(2, startDate);// 设置输入参数的值
				cs.setString(3, endDate);// 设置输入参数的值
				cs.setString(4, empId);// 设置输入参数的值
				cs.registerOutParameter(1, Types.VARCHAR);// 注册输出参数的类型
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				cs.execute();
				return cs.getString(1);// 获取输出参数的值
			}
		});
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public String CreateAttendanceDate(final String startDate, final String endDate) {
		return (String) jdbcTemp.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				String storedProc = "{? = call ATTENDANCE.CreateAttendanceDate(?,?)}";// 调用的sql
				CallableStatement cs = con.prepareCall(storedProc);
				cs.setString(2, startDate);// 设置输入参数的值
				cs.setString(3, endDate);// 设置输入参数的值
				cs.registerOutParameter(1, Types.VARCHAR);// 注册输出参数的类型
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				cs.execute();
				return cs.getString(1);// 获取输出参数的值
			}
		});
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public String CreateCleanRisk(final String auditProId, final String orgId) {
		return (String) jdbcTemp.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				String storedProc = "{? = call AUDITPRO.CreateCleanRisk(?,?)}";// 调用的sql
				CallableStatement cs = con.prepareCall(storedProc);
				cs.setString(2, auditProId);// 设置输入参数的值
				cs.setString(3, orgId);// 设置输入参数的值
				cs.registerOutParameter(1, Types.VARCHAR);// 注册输出参数的类型
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				cs.execute();
				return cs.getString(1);// 获取输出参数的值
			}
		});
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public String ChangeRiskAssignee(final String auditDtlId, final String userId) {
		return (String) jdbcTemp.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				String storedProc = "{? = call AUDITPRO.ChangeRiskAssignee(?,?)}";// 调用的sql
				CallableStatement cs = con.prepareCall(storedProc);
				cs.setString(2, auditDtlId);// 设置输入参数的值
				cs.setString(3, userId);// 设置输入参数的值
				cs.registerOutParameter(1, Types.VARCHAR);// 注册输出参数的类型
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				cs.execute();
				return cs.getString(1);// 获取输出参数的值
			}
		});
	}
}
